package com.kgc.dao;

import com.kgc.entity.UserPayment;
import com.kgc.untils.PageUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserPaymentDao extends BaseDao {
    /**
     * 连表查寻
     *
     * @param pageUtils
     * @param name
     * @return
     */
    public List<UserPayment> selectAll(PageUtils pageUtils, String name) {
        List<UserPayment> list = new ArrayList<>();
        try {
//            String sql = "select * from user_payment";
            String sql = "SELECT up.id,p.name,u.username,up.value,up.time,up.status FROM user_payment up INNER JOIN user u ON up.user_id=u.id INNER JOIN payment p ON up.payment_id=p.id";
            if (StringUtils.isNotEmpty(name)) {
                sql += " WHERE name LIKE'%" + name + "%'";
            }
            sql += " limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new UserPayment(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getTimestamp(5), rs.getInt(6)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;

    }

    /**
     * 查总数
     *
     * @param name
     * @return
     */
    public int countAll(String name) {
        try {
            String sql = "select count(*) from user_payment ";
            if (StringUtils.isNotEmpty(name)) {
                sql += " WHERE name LIKE '%" + name + "%'";
            }
            ResultSet rs = super.getExecuteQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    /**
     * 修改缴费状态
     * @param id
     * @return
     */
    public int updateStatus(Integer id) {
//        String sql = "update user_payment set status  = 1  where id =?";
        String sql = "UPDATE `user_payment` SET  `time` = now(), `status` = 1 WHERE `id` = ?";
        return super.getExecuteUpdate(sql, id);

    }

    /**
     * 添加缴费
     * @param userPayment
     * @return
     */
    public int addUserPayment(UserPayment userPayment) {
        String sql = "INSERT INTO `user_payment`(`user_id`, `payment_id`, `value`, `time`, `status`) VALUES (?, ? ,?, ?, ?)";
        return super.getExecuteUpdate(sql, userPayment.getUserId(), userPayment.getPaymentId(), userPayment.getValue(),userPayment.getTime(),userPayment.getStatus());
    }

    public UserPayment selectUserPaymentById(int id) {
        try {
            String sql = "select up.*,p.`name`,u.username from user_payment up left join user u on up.user_id = u.id left join payment p on up.payment_id = p.id where up.id = ?";
            ResultSet rs = super.getExecuteQuery(sql, id);
            if (rs.next()) {
                return new UserPayment(rs.getInt(1), rs.getInt(2),rs.getInt(3),rs.getString(4),rs.getTimestamp(5),rs.getInt(6),
                        rs.getString(7), rs.getString(8));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return null;
    }
}
